package com.icss.clothes.mapper;

import com.icss.clothes.entity.Color;
import com.icss.clothes.entity.Shop;
import com.icss.clothes.entity.Shopdetail;
import com.icss.clothes.service.imp.ShopService;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ShopMapper {
    @Select("select c.*,cd.c_price from commodity c left outer join commodity_detail cd " +
            "on c.c_id=cd.c_id where c.c_state='0' and c.c_picture1=cd.c_picture2 and cd_bha='1'")
    public List<Shop> selectAll();
     //通过商品备注检索获取特色商品的名字、价格、图片
    @Select("select c.*,c_price,c_picture2 " +
            "from commodity c left outer join commodity_detail cd " +
            "on c.c_id=cd.c_id where c_comment=#{comment} and c.c_state='0' and cd_bha='1' ")
    public List<Shop> findBycomment(String comment);

    //通过不同的排序得到商品
    @Select("select c.*,cd.c_price from commodity c left outer join commodity_detail cd " +
            "on c.c_id=cd.c_id where c.c_state='0' and cd_bha='1' order by ${sort} desc limit 0,6")
    public List<Shop> findCommodityBySort(String sort);
    //从数据库中查询得到所有满足过滤条件的商品列表
    @Select("<script>"+
            "select c.*,min(cd.c_price) minprice from commodity c left outer join commodity_detail cd on c.c_id=cd.c_id where c.c_state=0" +
            "<if test='type_id!=0'> and c.type_id=#{type_id}</if>"+
            " group by c.c_id" +
            "<if test='sorter!=null and sorter!=\"\"'>" +
            " order by ${sorter}" +
            "</if>"+
            "</script>")
    public List<Shop> selectAllSearch(Shop shopSearch);
    //从数据库中通过最高销量升序排序查询得到所有商品信息
    @Select("select c.*,cd.c_price from commodity c left outer join commodity_detail cd " +
            "on c.c_id=cd.c_id where c.c_state='0' and c.c_picture1=cd.c_picture2 and cd_bha='1' order by sale_number desc limit 0,6")
    public List<Shop> selectAllBySale_number();
    //通过指定的商品编号查询得到商品的详情信息
    @Select("select min(cd.c_price),cd.*,c.c_name,c.c_desc from commodity_detail cd left outer join commodity c " +
            "on c.c_id=cd.c_id where c.c_id=#{c_id} group by cd.c_picture2;")
    public List<Shopdetail> selectAllByC_cid(int c_id);
    //从数据库中通过指定的商品编号查询得到所有商品的颜色信息
    @Select("select cr.* from color cr left outer join commodity_detail cd on cr.color_id=cd.color_id " +
            "where cr.color_id=cd.color_id and cd.c_id=#{c_id} group by cr.color_id;")
    public List<Color> selectAllColorByC_id(int c_id);

    @Select("select c.*,min(d.c_price) c_price,cd_id from commodity c,commodity_detail d where c.c_id=d.c_id and c.c_id=#{id} GROUP BY c.c_id")
    public Shop selectById(int id);
//    @Select("select * from commodity_detail where c_id=#{c_id} and color_id=#{color_id} ")
@Select("select cd.*,s.size_name from commodity_detail cd left outer join size s on s.size_id=cd.size_id " +
        "where c_id=#{c_id} and color_id=#{color_id}")
    public List<Shopdetail> selectByCidAndColorId(@Param("c_id") int c_id, @Param("color_id") int color_id);
    @Select("select distinct c_price from commodity_detail where c_id=#{c_id} and size_id=#{size_id}")
    public double selectByCidAndSizeId(@Param("c_id") int c_id,
                                            @Param("size_id") int size_id);
    //
    @Select("select * from commodity_detail")
    public List<Shopdetail> selectAllByNumberAndCd_id();
}
